package com.study.common;

import com.study.utils.DateUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 导出Excel工具类-从联瑞crm拷贝过来
 *
 * @author chishengfeng
 * @version 2.0, 2017/8/23
 * @since 2.0
 */
public class ExcelUtils {

	private final static short DEFAULT_ROW_HEIGHT = 450;

	private final static int SHEET_DEFAULT_COLUMN_WIDTH = 15;

	/**
	 * @param name     Excel保存的主题名
	 * @param data     里边有Map和List Map存放字段对应关系(ziDuan,字段的第一个字符是序号)
	 * @param response
	 */
	public static void objListToExcel(String name, Map data, HttpServletResponse response) {
		Map<String, String> ziDuan = (Map<String, String>) data.get("ziDuan");
		List listData = (List) data.get("listData");
		Object[] keys = ziDuan.keySet().toArray();

		//循环外定义变量
		int lengthFirst = keys.length;
		String[] ziDuanKeys = new String[lengthFirst];
		String temp;
		int xuHao;
		for (int k = 0; k < lengthFirst; k++) {
			temp = keys[k].toString();
			xuHao = Integer.valueOf(temp.split("-")[0]);
			ziDuanKeys[xuHao] = temp.split("-")[1];
		}
		OutputStream out = null;
		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet(name);
			sheet.setDefaultColumnWidth(SHEET_DEFAULT_COLUMN_WIDTH);

			HSSFCellStyle style = wb.createCellStyle();
			// 设置表头样式
			style.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);//亮绿色
			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			// 设置表内容样式
			HSSFCellStyle style1 = wb.createCellStyle();
			style1.setFillForegroundColor(HSSFColor.WHITE.index);//亮绿色
			style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
			style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 生成另一个字体
			HSSFFont font2 = wb.createFont();
			font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
			// 把字体应用到当前的样式
			style.setFont(font2);
			style1.setFont(font2);
			//循环外定义变量
			HSSFCell cell;
			HSSFRow row;
			Object obj;
			String ziDuanName;
			Class clazz;
			Method[] methods;
			Pattern pattern;
			Matcher mat;
			Object shuXing;
			for (int i = 0, lengthSecond = listData.size(); i < lengthSecond; i++) {
				row = sheet.createRow(i);
				row.setHeight(DEFAULT_ROW_HEIGHT);
				obj = listData.get(i);
				for (int j = 0; j < lengthFirst; j++) {
					cell = row.createCell(j);
					if (i == 0) {
						cell.setCellStyle(style);//表头
						sheet.setColumnWidth(j, 6000);
						cell.setCellValue(new HSSFRichTextString(ziDuan.get(j + "-" + ziDuanKeys[j])));
					} else {
						cell.setCellStyle(style1);//
						ziDuanName = (String) ziDuanKeys[j];

						ziDuanName = ziDuanName.replaceFirst(ziDuanName.substring(0, 1), ziDuanName.substring(0, 1).toUpperCase());
						ziDuanName = "get" + ziDuanName;
						clazz = Class.forName(obj.getClass().getName());
						methods = clazz.getMethods();
						pattern = Pattern.compile(ziDuanName);
						for (Method m : methods) {
							mat = pattern.matcher(m.getName());
							if (mat.find()) {
								shuXing = m.invoke(obj, null);
								if (shuXing == null) {
									cell.setCellValue("");
								} else {
//									if (shuXing instanceof Date) {
//										shuXing = DateUtil.DateToString(((Date) shuXing), "yyyy-MM-dd HH:mm:ss");
//									}
//									if ("getStatus".equals(m.getName())) {
//										//订单状态枚举key-value装换
//										shuXing = SubOrderStatusEnum.getTypeByCode(Integer.parseInt(shuXing.toString())).getDesc();
//									}
//									if ("getGoodsInstStatus".equals(m.getName())) {
//										//商品实例状态枚举key-value装换
//										shuXing = GoodsStatusOfBuyerEnum.getDescription(Integer.parseInt(shuXing.toString()));
//									}
									if ("getPriceChanged".equals(m.getName())) {
										//是否被改价
										if (0 == Integer.parseInt(shuXing.toString())) {
											shuXing = "否";
										} else {
											shuXing = "是";
										}
									}
									cell.setCellValue(shuXing.toString());// 这里可以做数据格式处理
								}
								break;
							}
						}
					}
				}
			}
			String fileTime = DateUtil.DateToString(new Date(), "yyyyMMddHHmmss");
			String filename = fileTime + ".xls";
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment;filename=" + filename);
			out = response.getOutputStream();
			wb.write(out);// 写入File
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(out);
		}
	}

	public static void errorMsgToExcel(String name, String errorMsg, HttpServletResponse response) {
		OutputStream out = null;
		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet(name);
			sheet.setDefaultColumnWidth(SHEET_DEFAULT_COLUMN_WIDTH);

			HSSFCellStyle style = wb.createCellStyle();
			// 设置表头样式
			style.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);//亮绿色
			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			// 设置表内容样式
			HSSFCellStyle style1 = wb.createCellStyle();
			style1.setFillForegroundColor(HSSFColor.WHITE.index);//亮绿色
			style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
			style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 生成另一个字体
			HSSFFont font2 = wb.createFont();
			font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
			// 把字体应用到当前的样式
			style.setFont(font2);
			style1.setFont(font2);
			//循环外定义变量
			HSSFCell cell;
			HSSFRow row;

			row = sheet.createRow(0);
			row.setHeight(DEFAULT_ROW_HEIGHT);
			cell = row.createCell(0);
			cell.setCellStyle(style);//表头
			sheet.setColumnWidth(0, 6000);
			cell.setCellValue(new HSSFRichTextString("错误信息:"));

			row = sheet.createRow(1);
			cell = row.createCell(0);
			cell.setCellStyle(style1);
			cell.setCellValue(errorMsg);

			String fileTime = DateUtil.DateToString(new Date(), "yyyyMMddHHmmss");
			String filename = fileTime + ".xls";
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment;filename=" + filename);
			out = response.getOutputStream();
			wb.write(out);// 写入File
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			close(out);
		}
	}

	public static void close(OutputStream out) {
		if (out != null) {
			try {
				out.flush();
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
}
